﻿Imports System
Imports System.Data
Imports MySql.Data.Types
Imports System.Configuration
Imports MySql.Data.MySqlClient

Public Class DataBaseAccess

  ' Represents an open connection to a MySQL database
  Public MySqlCon As MySqlConnection
  ' Represents a MySQL statement or stored
  ' procedure to execute against a MySQL database
  Public MySqlCom As MySqlCommand
  ' Provides a way of reading a forward-only
  ' stream of rows from a MySQL database
  Public MySqldreader As MySqlDataReader
  ' Represents a set of data commands and a database
  ' connection that are used to fill the 
  Public MySqlDa As MySqlDataAdapter
  ' Automatically generates single-table commands that are
  ' used to reconcile changes made to a 
  Public MySqlComb As MySqlCommandBuilder

  'Purpose: Class constructor.
  Public Sub New()
    'Initialize the class' members.
    MySqlCon = New MySqlConnection
    ' Set connection string of the MySqlConnection object
    MySqlCon.ConnectionString = MyConnectionString
  End Sub

  ' Purpose: Opens a database connection
  ' if MySQL connection open successful then return true
  ' else occur exception return false
  Public Function OpenConnection() As Boolean
    Dim RetValue As Boolean = False
    Try ' open connection
      If (MySqlCon.State <> ConnectionState.Open) Then
        MySqlCon.ConnectionString = MyConnectionString
        MySqlCon.Open()
        RetValue = True
      Else
        RetValue = True
      End If
    Catch ex As MySqlException
      'MessageBox.Show(My.Settings.IDS_DATABASE_CONNECT_ERROR + ex.ToString(), My.Settings.IDS_MACHINE_MANAGEMENT, MessageBoxButtons.OK, MessageBoxIcon.Error)
      RetValue = False
    End Try
    Return RetValue
  End Function

  Function ReadExcellFile(ByVal FileName As String, ByVal SheetName As String) As DataTable
    Dim strConn As String = Nothing
    Dim tblSheet As DataTable = New DataTable()
    strConn = ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= ") & FileName & "; Extended Properties=Excel 8.0;"
    Dim ObjConn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(strConn)
    Try
      ObjConn.Open()

      Dim ObjCmd As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & SheetName & "$]", ObjConn)
      Dim objDA As New System.Data.OleDb.OleDbDataAdapter()
      objDA.SelectCommand = ObjCmd

      Dim ObjDataSet As New DataSet()
      objDA.Fill(ObjDataSet)

      ObjConn.Close()
      tblSheet = ObjDataSet.Tables(0)
    Catch excepcion As Exception
      If ObjConn.State = ConnectionState.Open Then
        ObjConn.Close()
      End If
      'Posible error incluido al leer el archivo Excel, puede estar bloquedo.
      MessageBox.Show("Error: ", excepcion.Message)
      Return Nothing
    End Try
    Return tblSheet
  End Function

  ' Purpose: Close a database connection. 
  ' if MySQL connection close successful then return true else
  ' occur exception return false
  Public Function CloseConnection() As Boolean
    Try ' Close connection
      If MySqlCon.State <> ConnectionState.Closed Then
        MySqlCon.Close()
        Return True
      Else
        Return False
      End If
    Catch ex As MySqlException
      'MessageBox.Show(My.Settings.IDS_DATABASE_CLOSE_ERROR + ex.ToString(), My.Settings.IDS_MACHINE_MANAGEMENT, MessageBoxButtons.OK, MessageBoxIcon.Error)
      Return False
    End Try
  End Function
  ' Purpose: ExecuteNoneQuery command
  ' if MySQL statement execute successful then return true else
  ' occur exception retun false
  Public Function ExecuteNoneQuery(ByVal v_sSqlString As String, Optional ByVal ShowErrorMessage As Boolean = False) As Boolean
    If OpenConnection() Then
      Try
        'sets the MySqlConnection used by this MySqlCom of the MySqlCommand
        MySqlCom = New MySqlCommand(v_sSqlString, MySqlCon)
        ' Executes a SQL statement against the connection and returns
        ' the number of rows affected.
        MySqlCom.ExecuteNonQuery()
        Return True
      Catch ex As MySqlException
        If ShowErrorMessage Then
          'MessageBox.Show(My.Settings.IDS_DATABASE_EXECUTENONQUERY_ERROR + ex.ToString(), My.Settings.IDS_MACHINE_MANAGEMENT, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        Return False
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlCom.Dispose()
      End Try
    Else
      Return False
    End If
  End Function

  ' Purpose: Provides a way of reading a forward-only stream of rows from a MySQL database
  ' if MySQL statement execute successful then return MySqlDataReader else
  ' occur exception return nothing
  Public Function GetDataReader(ByVal v_sSqlString As String) As MySqlDataReader
    Try
      If OpenConnection() Then
        'sets the MySqlConnection used by this MySqlCom of the MySqlCommand
        MySqlCom = New MySqlCommand(v_sSqlString, MySqlCon)
        ' Sends the CommandText to the Connection and builds a MySqlDataReader.
        MySqldreader = MySqlCom.ExecuteReader
      End If
      Return MySqldreader
    Catch ex As MySqlException
      'MessageBox.Show(My.Settings.IDS_DATABASE_EXECUTEREADER_ERROR + ex.ToString(), My.Settings.IDS_MACHINE_MANAGEMENT, MessageBoxButtons.OK, MessageBoxIcon.Error)
      Return Nothing
    Finally
      ' Close database connection.
      CloseConnection()
      MySqlCom.Dispose()
    End Try
  End Function
  'Purpose: return the first column of the first row in the result set,
  ' or a nothing reference if the result set is empty
  Public Function GetScalar(ByVal v_sSqlString As String) As Object
    Dim MyResult As Object = Nothing
    Try
      If OpenConnection() Then
        'sets the MySqlConnection used by this MySqlCom of the MySqlCommand
        MySqlCom = New MySqlCommand(v_sSqlString, MySqlCon)
        ' Executes the query, and returns the first column of the first row in the
        ' result set returned by the query. Extra columns or rows are ignored.
        MyResult = MySqlCom.ExecuteScalar
      End If
    Catch ex As Exception
      'MessageBox.Show(My.Settings.IDS_DATABASE_EXECUTESCALAR_ERROR + ex.ToString(), My.Settings.IDS_MACHINE_MANAGEMENT, MessageBoxButtons.OK, MessageBoxIcon.Error)
    Finally
      ' Close database connection.
      CloseConnection()
      MySqlCom.Dispose()
    End Try
    Return MyResult
  End Function

  Public Function GetDataTable(ByVal TableName As String, ByVal Condition As String) As DataTable

    Dim dtable As New DataTable(TableName)
    MySqlDa = New MySqlDataAdapter("Select * from " & TableName, MySqlCon)
    If OpenConnection() Then
      Try
        MySqlDa.Fill(dtable)
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_MACHINE_MANAGER_DATABASE)
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
      End Try
    End If
    Return dtable
  End Function

  ' Purpose: Return one table of in-memory data.
  Public Function GetDataTable(ByVal v_sSqlString As String) As DataTable
    Dim sTableName As String = String.Empty
    Try
      sTableName = v_sSqlString.Substring(v_sSqlString.IndexOf(" FROM ", System.StringComparison.CurrentCultureIgnoreCase) + 6)
      sTableName = sTableName.Substring(0, sTableName.IndexOf(" "))
    Catch ex As Exception
      sTableName = "TableName"
    End Try

    Dim dtable As New DataTable(sTableName)
    MySqlDa = New MySqlDataAdapter(v_sSqlString, MySqlCon)
    If OpenConnection() Then
      Try
        MySqlDa.Fill(dtable)
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_MACHINE_MANAGER_DATABASE)
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
      End Try
    End If
    Return dtable
  End Function

  'Purpose: Get one table from procedure in database
  Public Function GetDataTable(ByVal ProcedureName As String, _
      ByVal parameterName1 As String, ByVal Value1 As String, _
      ByVal parameterName2 As String, ByVal Value2 As String, _
      ByVal parameterName3 As String, ByVal Value3 As String) As DataTable
    Dim dtable As New DataTable(ProcedureName)
    MySqlDa = New MySqlDataAdapter(ProcedureName, MySqlCon)
    MySqlDa.SelectCommand.CommandType = CommandType.StoredProcedure
    MySqlDa.SelectCommand.Parameters.AddWithValue(parameterName1, Value1)
    MySqlDa.SelectCommand.Parameters.AddWithValue(parameterName2, Value2)
    MySqlDa.SelectCommand.Parameters.AddWithValue(parameterName3, Value3)
    If OpenConnection() Then

      Try
        MySqlDa.Fill(dtable)
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_MACHINE_MANAGER_DATABASE)
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
      End Try
    End If
    Return dtable
  End Function

  'Purpose: Get one table from procedure in database
  Public Function GetDataTableFromProcedure(ByVal ProcedureName As String) As DataTable
    Dim dtable As New DataTable(ProcedureName)
    MySqlDa = New MySqlDataAdapter(ProcedureName, MySqlCon)
    MySqlDa.SelectCommand.CommandType = CommandType.StoredProcedure
    If OpenConnection() Then
      Try
        MySqlDa.Fill(dtable)
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_MACHINE_MANAGER_DATABASE)
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
      End Try
    End If
    Return dtable
  End Function

  ' Purpose: Return an in-memory cache of data.
  Public Function GetDataSet(ByVal v_sSqlString As String, ByVal v_sTableName _
      As String) As DataSet
    Dim dset As New DataSet
    MySqlDa = New MySqlDataAdapter(v_sSqlString, MySqlCon)
    If OpenConnection() Then
      Try
        ' Configures the schema to match that in the data source based on 
        ' the specified System.Data.SchemaType
        MySqlDa.FillSchema(dset, SchemaType.Source)
        MySqlDa.Fill(dset, v_sTableName)
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_MACHINE_MANAGER_DATABASE)
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
      End Try
    End If
    Return dset
  End Function
  ' Calls the respective INSERT, UPDATE, or DELETE statements for each inserted,
  ' updated, or deleted row in the specified DataTable. if execute success
  ' return true else return false
  Public Function UpdateDataTable(ByVal v_sSqlString As String, ByVal v_dtable As  _
  System.Data.DataTable, Optional ByVal ShowErrorMessage As Boolean = False) As Boolean
    MySqlDa = New MySqlDataAdapter
    MySqlDa.SelectCommand = New MySqlCommand(v_sSqlString, MySqlCon)
    ' Without the MySQLCommandBuilder this line would fail.
    MySqlComb = New MySqlCommandBuilder(MySqlDa)
    Try
      If OpenConnection() Then
        MySqlDa.Update(v_dtable)
        Return True
      Else
        Return False
      End If
    Catch ex As Exception
      If ShowErrorMessage Then
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_WARNING)
      End If
      Return False
    Finally
      ' Close database connection.
      CloseConnection()
      MySqlDa.Dispose()
      MySqlComb.Dispose()
    End Try
  End Function

  ' Purpose: Represents an in-memory cache of data.
  ' if object exist return true else return false
  Public Function IsEmpty(ByVal v_sSqlString As String) As Boolean
    If OpenConnection() Then
      MySqlDa = New MySqlDataAdapter(v_sSqlString, MySqlCon)
      Dim dtable As New DataTable
      Try
        MySqlDa.Fill(dtable)
        If dtable.Rows.Count > 0 Then
          Return False
        Else
          Return True
        End If
      Catch ex As MySqlException
        'MsgBox(ex.Message, MsgBoxStyle.Critical, My.Settings.IDS_WARNING)
        Return False
      Finally
        ' Close database connection.
        CloseConnection()
        MySqlDa.Dispose()
        dtable.Dispose()
      End Try
    Else
      Return False
    End If

  End Function
  'Purpose: Implements the IDispose' method Dispose.
  Protected Overloads Sub Dispose()
    CloseConnection()
    ' Requests that the system not call the finalizer for the specified object
    GC.SuppressFinalize(Me)
  End Sub

End Class
